﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Aulac.Global.Xml;
using Aulac.Rsc.Ems.Tags;
using Aulac.Global.Common;
using Aulac.Rsc.Ems;
using Aulac.Srv.Db.Common;

namespace Aulac.Srv.Db.Data.Ems.Stores.RptSotes
{
    partial class RptStoreMng
    {
        /// <summary>
        /// Get Data Bang Ke Phieu Nhap Kho
        /// </summary>
        private XmlDataDocument GetDbListRecStoreVch(string pStorePrkID, string pVchDateFrom, string pVchDateTo)
        {
            /* Author Write: H.Rin */
            /* Code Update: 01/18/2011 */

            Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData query = new Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData(this.DbDataProvider);
            query.SetVchLinesElm(QueryCmdTypes.SelectData);
            string[] arrH = new string[]{ 
                                         tagVch.Inv.VchDate,
                                         tagVch.Inv.VchNo,
                                         tagVch.Inv.Note};
            
            
            string sqlQuery = string.Format("SELECT DISTINCT Rh.{0}", StringHelper.ConvertArrayToString(", Rh.", arrH));
            sqlQuery += string.Format(", SUM(Rl.{0}) AS {0}", tagVch.Inv.AmtLineInExc);
            sqlQuery += string.Format(", SUM(Rl.{0}) AS {0}", tagVch.Inv.AmtTaxLineInExc);
            sqlQuery += string.Format(", SUM(Rl.{0}) AS {0}", tagVch.Inv.AmtTtlLineInExc);

            sqlQuery += string.Format(" FROM {0} AS Rh RIGHT OUTER JOIN {1} AS Rl ON Rh.{2} = Rl.{2}", TableNames.VchRecInvHeader, TableNames.VchRecInvLine, tagVch.Inv.VchRecInvPrkID);
            sqlQuery += string.Format(" WHERE (Rl.{0} = @{0})", tagVch.Inv.StorePrkID);
            sqlQuery += string.Format(" AND (Rh.{0} BETWEEN @{1} AND @{2})", tagVch.Inv.VchDate, tagVch.Inv.VchDateFrom, tagVch.Inv.VchDateTo);
            sqlQuery += string.Format(" GROUP BY Rh.{0}, Rl.{1}", StringHelper.ConvertArrayToString(", Rh.", arrH), tagVch.Inv.StorePrkID);
            sqlQuery += string.Format(" ORDER BY Rh.{0}, Rh.{1}", tagVch.Inv.VchDate, tagVch.Inv.VchNo);

            query.ObjQueryVchLines.SqlQueryString = sqlQuery;
            query.ObjQueryVchLines.AddValueParamFilterItem(tagItems.Store.StorePrkID, tagItems.Store.StorePrkID, pStorePrkID);
            query.ObjQueryVchLines.AddValueParamFilterItem(tagVch.Inv.VchDateFrom, tagVch.Inv.VchDateFrom, pVchDateFrom);
            query.ObjQueryVchLines.AddValueParamFilterItem(tagVch.Inv.VchDateTo, tagVch.Inv.VchDateTo, pVchDateTo);

            return query.ExecureQuery();
        }

        /// <summary>
        /// Get Data Bang Ke Phieu Xuat Kho
        /// </summary>
        private XmlDataDocument GetDbListRelsStoreVch(string pStorePrkID, string pVchDateFrom, string pVchDateTo)
        {
            /* Author Write: H.Rin */
            /* Code Update: 01/18/2011 */

            string[] arrH = new string[]{ 
                                         tagVch.Inv.VchDate,
                                         tagVch.Inv.VchNo,
                                         tagVch.Inv.Note};

            string sqlQuery = string.Format("SELECT DISTINCT Rh.{0}", StringHelper.ConvertArrayToString(", Rh.", arrH));
            sqlQuery += string.Format(", SUM(Rl.{0}) AS {0}", tagVch.Inv.AmtLineInExc);
            sqlQuery += string.Format(", SUM(Rl.{0}) AS {0}", tagVch.Inv.AmtTaxLineInExc);
            sqlQuery += string.Format(", SUM(Rl.{0}) AS {0}", tagVch.Inv.AmtTtlLineInExc);

            sqlQuery += string.Format(" FROM {0} AS Rh RIGHT OUTER JOIN {1} AS Rl ON Rh.{2} = Rl.{2}", TableNames.VchRelsInvHeader, TableNames.VchRelsInvLine, tagVch.Inv.VchRelsInvPrkID);

            sqlQuery += string.Format(" WHERE (Rh.{0} BETWEEN ? AND ?) AND (Rh.{1} = ?)", tagVch.Inv.VchDate, tagVch.Inv.StorePrkID);//CONVERT(DATETIME, ?, 102)
            sqlQuery += string.Format(" GROUP BY Rh.{0}, Rl.{1}", StringHelper.ConvertArrayToString(", Rh.", arrH), tagVch.Inv.StorePrkID);
            sqlQuery += string.Format(" ORDER BY Rh.{0}, Rh.{1}", tagVch.Inv.VchDate, tagVch.Inv.VchNo);

            DbDataProvider.DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pVchDateFrom, pVchDateTo, pStorePrkID);

            XmlDataDocument xmlDoc = DbDataProvider.CreateXmlDocument("", "");
            while (DbDataProvider.DbDataReader.Read())
            {
                XmlElement eLine = XmlProcess.AddVchLineItemElm(xmlDoc);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrH);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, tagVch.Inv.AmtLineInExc, tagVch.Inv.AmtTaxLineInExc, tagVch.Inv.AmtTtlLineInExc);
            }
            DbDataProvider.DbDataReader.Close();
            return xmlDoc;
        }

        /// <summary>
        /// Get Detail List Phieu Nhap Kho
        /// </summary>
        private XmlDataDocument GetDetailDbListRecStoreVch(string pStorePrkID, string pVchDateFrom, string pVchDateTo)
        {
            /* Author Write: H.Rin */
            /* Code Update: 01/18/2011 */

            string[] arrH = new string[]{ 
                                         tagVch.Inv.VchDate,
                                         tagVch.Inv.VchNo,
                                         tagVch.Inv.Note};
            string[] arrL = new string[]{
                                         tagVch.Inv.Quantity,
                                         tagVch.Inv.PriceBuyInExc,
                                         tagVch.Inv.AmtLineInExc,
                                         tagVch.Inv.AmtTaxLineInExc,
                                         tagVch.Inv.AmtTtlLineInExc};
            string[] arrI = new string[]{
                                         tagItems.Item.ItemID,
                                         tagItems.Item.ItemName,
                                         tagItems.Item.Unit};

            string sqlQuery = string.Format("SELECT DISTINCT Rh.{0}", StringHelper.ConvertArrayToString(", Rh.", arrH));
            sqlQuery += string.Format(", Rl.{0}", StringHelper.ConvertArrayToString(", Rl.",arrL));
            sqlQuery += string.Format(", I.{0}", StringHelper.ConvertArrayToString(", I.", arrI));

            sqlQuery += string.Format(" FROM {0} AS I", TableNames.Dm_MdnItems);
            sqlQuery += string.Format(" RIGHT OUTER JOIN {0} AS Rl ON I.{1} = Rl.{2}", TableNames.VchRecInvLine, tagItems.Item.ItemPrkID, tagVch.Inv.ItemPrkID);
            sqlQuery += string.Format(" RIGHT OUTER JOIN {0} AS Rh ON Rl.{1} = Rh.{1}", TableNames.VchRecInvHeader, tagVch.Inv.VchRecInvPrkID);

            sqlQuery += string.Format(" WHERE (Rh.{0} BETWEEN ? AND ?) AND (Rl.{1} = ?)", tagVch.Inv.VchDate, tagVch.Inv.StorePrkID);
            sqlQuery += string.Format(" ORDER BY Rh.{0}, Rh.{1}, I.{2}", tagVch.Inv.VchNo, tagVch.Inv.VchDate, tagItems.Item.ItemID);

            DbDataProvider.DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pVchDateFrom, pVchDateTo, pStorePrkID);

            XmlDataDocument xmlDoc = DbDataProvider.CreateXmlDocument("", "");
            while (DbDataProvider.DbDataReader.Read())
            {
                XmlElement eLine = XmlProcess.AddVchLineItemElm(xmlDoc);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrH);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrL);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrI);
            }
            DbDataProvider.DbDataReader.Close();
            return xmlDoc;
        }

        /// <summary>
        /// Get Detail List Phieu Xuat Kho
        /// </summary>
        private XmlDataDocument GetDetailDbListRelsStoreVch(string pStorePrkID, string pVchDateFrom, string pVchDateTo)
        {
            /* Author Write: H.Rin */
            /* Code Update: 01/18/2011 */

            string[] arrH = new string[]{ 
                                         tagVch.Inv.VchDate,
                                         tagVch.Inv.VchNo,
                                         tagVch.Inv.Note};
            string[] arrL = new string[]{
                                         tagVch.Inv.Quantity,
                                         tagVch.Inv.PriceBuyInExc,
                                         tagVch.Inv.AmtLineInExc,
                                         tagVch.Inv.AmtTaxLineInExc,
                                         tagVch.Inv.AmtTtlLineInExc};
            string[] arrI = new string[]{
                                         tagItems.Item.ItemID,
                                         tagItems.Item.ItemName,
                                         tagItems.Item.Unit};

            string sqlQuery = string.Format("SELECT DISTINCT Rh.{0}", StringHelper.ConvertArrayToString(", Rh.", arrH));
            sqlQuery += string.Format(", Rl.{0}", StringHelper.ConvertArrayToString(", Rl.", arrL));
            sqlQuery += string.Format(", I.{0}", StringHelper.ConvertArrayToString(", I.", arrI));

            sqlQuery += string.Format(" FROM {0} AS I", TableNames.Dm_MdnItems);
            sqlQuery += string.Format(" RIGHT OUTER JOIN {0} AS Rl ON I.{1} = Rl.{2}", TableNames.VchRelsInvLine, tagItems.Item.ItemPrkID, tagVch.Inv.ItemPrkID);
            sqlQuery += string.Format(" RIGHT OUTER JOIN {0} AS Rh ON Rl.{1} = Rh.{1}", TableNames.VchRelsInvHeader, tagVch.Inv.VchRelsInvPrkID);

            sqlQuery += string.Format(" WHERE (Rh.{0} BETWEEN ? AND ?) AND (Rl.{1} = ?)", tagVch.Inv.VchDate, tagVch.Inv.StorePrkID);
            sqlQuery += string.Format(" ORDER BY Rh.{0}, Rh.{1}, I.{2}", tagVch.Inv.VchNo, tagVch.Inv.VchDate, tagItems.Item.ItemID);

            DbDataProvider.DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pVchDateFrom, pVchDateTo, pStorePrkID);

            XmlDataDocument xmlDoc = DbDataProvider.CreateXmlDocument("", "");
            while (DbDataProvider.DbDataReader.Read())
            {
                XmlElement eLine = XmlProcess.AddVchLineItemElm(xmlDoc);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrH);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrL);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, arrI);
            }
            DbDataProvider.DbDataReader.Close();
            return xmlDoc;
        }

        private XmlDataDocument GetStoreName(string pStorePrkID)
        {
            string sqlQuery = string.Format("SELECT {0} FROM {1} WHERE {2} = ?", tagItems.Store.StoreName, TableNames.Dm_MdnStores, tagItems.Store.StorePrkID);
            DbDataProvider.DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pStorePrkID);

            XmlDataDocument xmlDoc = DbDataProvider.CreateXmlDocument("", "");
            while (DbDataProvider.DbDataReader.Read())
            {
                XmlElement eLine = XmlProcess.AddVchHeaderElm(ref xmlDoc);
                DbDataProvider.GetValuesToXml(DbDataProvider.DbDataReader, ref eLine, tagItems.Store.StoreName);
            }
            DbDataProvider.DbDataReader.Close();
            return xmlDoc;
        }
    }
}
